package com.example.libraryapp.db;

import com.example.libraryapp.R;
import com.example.libraryapp.util.Constance;
import com.example.libraryapp.util.MyConfig;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

/**
 * 数据库帮助类
 */
public class MySqliteHelper extends SQLiteOpenHelper {

	// 缓存数据库操作类的单例
	private static volatile MySqliteHelper instance;
	// 数据库名和版本号
	private final static String DB_NAME = "library.db";
	private final static int DB_VERSION = 1;
	private Context context;
	// 数据库对象
	private SQLiteDatabase db;
	private String path = Environment.getExternalStorageDirectory() + "/";

	/**
	 * 对外公开得到实例的方法
	 * 
	 * @param context
	 *            上下文
	 * @return 操作缓存数据库的实例
	 */
	public static MySqliteHelper getInstance(Context context) {

		if (instance == null) {
			synchronized (MySqliteHelper.class) {
				if (instance == null) {
					instance = new MySqliteHelper(context);
				}
			}
		}
		return instance;
	}

	/**
	 * 默认的构造方法
	 * 
	 * @param context
	 */
	public MySqliteHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		try {
			this.context = context;
			// 打开或创建数据库
			db = SQLiteDatabase.openOrCreateDatabase(path + DB_NAME, null);
			if (!MyConfig.getDbCreated(context)) {
				createDataTable(db, context);
				MyConfig.setDbCreated(context);
			}
		} catch (Exception e) {
			e.printStackTrace();
			db = this.getWritableDatabase();
		}

	}

	/**
	 * 创建表
	 * 
	 * @param db
	 */
	private void createDataTable(SQLiteDatabase db, Context context) {
		if (db == null) {
			return;
		}
		try {
			createUserTable(db);
			createBookTable(db); // 图书表
			createLendTable(db);
			createNoticeTable(db);
			createNoticeDetailTable(db, context);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 创建用户表
	 */
	private void createUserTable(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + Constance.USER_TABLE_NMAE);
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constance.USER_TABLE_NMAE
				+ " (urid  integer primary key autoincrement, cardid varchar,username varchar,password varchar)");
		db.execSQL("INSERT INTO " + Constance.USER_TABLE_NMAE
				+ " ( cardid, username, password) VALUES ('121003410809','李松蔚','123456')");
	}

	/**
	 * 创建图书表
	 */
	private void createBookTable(SQLiteDatabase db) {
		// 创建图书表
		db.execSQL("DROP TABLE IF EXISTS " + Constance.BOOK_TABLE_NMAE);
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constance.BOOK_TABLE_NMAE
				+ " (id integer NOT NULL PRIMARY KEY autoincrement, imageid integer,name varchar, author varchar, publishing varchar)");
		db.execSQL("INSERT INTO " + Constance.BOOK_TABLE_NMAE
				+ " ( imageid, name, author, publishing) VALUES ("
				+ R.drawable.first_code + ",'第一行代码','郭林','人民邮电出版社')");
		db.execSQL("INSERT INTO " + Constance.BOOK_TABLE_NMAE
				+ " ( imageid, name, author, publishing) VALUES ("
				+ R.drawable.crazy_android + ",'Android疯狂讲义','李刚','电子工业出版社')");
		db.execSQL("INSERT INTO " + Constance.BOOK_TABLE_NMAE
				+ " ( imageid, name, author, publishing) VALUES ("
				+ R.drawable.b3 + ",'Java编程思想(第4版)','埃史尔','电子工业出版社')");
		db.execSQL("INSERT INTO " + Constance.BOOK_TABLE_NMAE
				+ " ( imageid, name, author, publishing) VALUES ("
				+ R.drawable.b4 + ",'Java从入门到精通(第3版)','明日科技','清华大学出版社')");
		db.execSQL("INSERT INTO " + Constance.BOOK_TABLE_NMAE
				+ " ( imageid, name, author, publishing) VALUES ("
				+ R.drawable.b5 + ",'疯狂Java讲义(第3版)','李刚','电子工业出版社')");
		db.execSQL("INSERT INTO " + Constance.BOOK_TABLE_NMAE
				+ " ( imageid, name, author, publishing) VALUES ("
				+ R.drawable.b6 + ",'Java核心技术·卷2','霍斯特曼','机械工业出版社')");

		db.execSQL("INSERT INTO " + Constance.BOOK_TABLE_NMAE
				+ " ( imageid, name, author, publishing) VALUES ("
				+ R.drawable.b7 + ",'Android编程权威指南','菲利普斯','人民邮电出版社')");
		db.execSQL("INSERT INTO " + Constance.BOOK_TABLE_NMAE
				+ " ( imageid, name, author, publishing) VALUES ("
				+ R.drawable.b8 + ",'Android 4高级编程','迈耶','人民邮电出版社')");

		db.execSQL("INSERT INTO " + Constance.BOOK_TABLE_NMAE
				+ " ( imageid, name, author, publishing) VALUES ("
				+ R.drawable.b9 + ",'Android安全攻防权威指南 ','德雷克','人民邮电出版社')");
		db.execSQL("INSERT INTO " + Constance.BOOK_TABLE_NMAE
				+ " ( imageid, name, author, publishing) VALUES ("
				+ R.drawable.b10 + ",'Android开发高手进阶','任玉刚','机械工业出版社')");

	}

	/**
	 * 创建借阅表
	 */
	private void createLendTable(SQLiteDatabase db) {
		// 创建图书表
		db.execSQL("DROP TABLE IF EXISTS " + Constance.LEND_TABLE_NMAE);
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constance.LEND_TABLE_NMAE
				+ " (id integer primary key autoincrement, bookid varchar,cardid varchar, username varchar, starting varchar, expiring varchar)");
	}

	/**
	 * 创建公告表
	 */
	private void createNoticeTable(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + Constance.NOTICE_TABLE_NMAE);
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constance.NOTICE_TABLE_NMAE
				+ " (id integer primary key autoincrement, title varchar,pubtime varchar)");
		db.execSQL("INSERT INTO "
				+ Constance.NOTICE_TABLE_NMAE
				+ " ( title, pubtime) VALUES ('学校召开六届二次图书情报工作委员会会议','2016-03-15')");
		db.execSQL("INSERT INTO "
				+ Constance.NOTICE_TABLE_NMAE
				+ " ( title, pubtime) VALUES ('关于本学期临港图书馆考研自习室开放的通知','2016-03-12')");
		db.execSQL("INSERT INTO " + Constance.NOTICE_TABLE_NMAE
				+ " ( title, pubtime) VALUES ('图书馆举行年终总结及迎新茶话会','2016-01-21')");
	}

	/**
	 * 创建公告详情表
	 */
	private void createNoticeDetailTable(SQLiteDatabase db, Context context) {
		db.execSQL("DROP TABLE IF EXISTS " + Constance.NOTICEDETAIL_TABLE_NMAE);
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constance.NOTICEDETAIL_TABLE_NMAE
				+ " (id integer primary key autoincrement, did integer, content varchar,imageid integer)");
		// 第一个
		db.execSQL("INSERT INTO " + Constance.NOTICEDETAIL_TABLE_NMAE
				+ " ( did, content, imageid) VALUES (1, '"
				+ context.getResources().getString(R.string.notice_detail_1_1)
				+ "'," + R.drawable.g1 + ")");
		db.execSQL("INSERT INTO " + Constance.NOTICEDETAIL_TABLE_NMAE
				+ " ( did, content, imageid) VALUES (1, '"
				+ context.getResources().getString(R.string.notice_detail_1_2)
				+ "',-1)");
		db.execSQL("INSERT INTO " + Constance.NOTICEDETAIL_TABLE_NMAE
				+ " ( did, content, imageid) VALUES (1, '"
				+ context.getResources().getString(R.string.notice_detail_1_3)
				+ "',-1)");
		// 第二个
		db.execSQL("INSERT INTO " + Constance.NOTICEDETAIL_TABLE_NMAE
				+ " ( did, content, imageid) VALUES (2, '"
				+ context.getResources().getString(R.string.notice_detail_2_1)
				+ "',-1)");
		db.execSQL("INSERT INTO " + Constance.NOTICEDETAIL_TABLE_NMAE
				+ " ( did, content, imageid) VALUES (2, '"
				+ context.getResources().getString(R.string.notice_detail_2_2)
				+ "',-1)");
		db.execSQL("INSERT INTO " + Constance.NOTICEDETAIL_TABLE_NMAE
				+ " ( did, content, imageid) VALUES (2, '"
				+ context.getResources().getString(R.string.notice_detail_2_3)
				+ "',-1)");
		db.execSQL("INSERT INTO " + Constance.NOTICEDETAIL_TABLE_NMAE
				+ " ( did, content, imageid) VALUES (2, '"
				+ context.getResources().getString(R.string.notice_detail_2_4)
				+ "',-1)");
		// 第三个
		db.execSQL("INSERT INTO " + Constance.NOTICEDETAIL_TABLE_NMAE
				+ " ( did, content, imageid) VALUES (3, '"
				+ context.getResources().getString(R.string.notice_detail_3_1)
				+ "'," + R.drawable.g3 + ")");
		db.execSQL("INSERT INTO " + Constance.NOTICEDETAIL_TABLE_NMAE
				+ " ( did, content, imageid) VALUES (3, '"
				+ context.getResources().getString(R.string.notice_detail_3_2)
				+ "',-1)");
		db.execSQL("INSERT INTO " + Constance.NOTICEDETAIL_TABLE_NMAE
				+ " ( did, content, imageid) VALUES (3, '"
				+ context.getResources().getString(R.string.notice_detail_3_3)
				+ "',-1)");
	}

	/**
	 * 生命周期-创建方法
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	/**
	 * 生命周期-更新方法
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	/**
	 * 更新方法
	 */
	public int update(String table, ContentValues values, String whereClause,
			String[] whereArgs) {
		try {
			return db.update(table, values, whereClause, whereArgs);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return 0;
	}

	/**
	 * 插入
	 */
	public synchronized long insert(String tableName, String columnName,
			ContentValues values) {
		try {
			return db.insert(tableName, columnName, values);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * 多个插入
	 */
	public void insert(String tableName, String columnName,
			ContentValues[] values) {
		try {
			for (int i = 0; i < values.length; i++) {
				insert(tableName, columnName, values[i]);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 查询方法
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public synchronized Cursor query(String tableName, String where) {
		String result = null;

		String sql = "select * from " + tableName + " where " + where;
		if (db != null) {
			Cursor cursor = db.rawQuery(sql, null);
			return cursor;
		}
		return null;
	}

	/**
	 * 从数据库中取出缓存的json数据
	 * 
	 * @param key
	 * @return value-json缓存
	 */
	public synchronized String getCacheData(String key) {
		if (key == null)
			return null;
		String result = null;
		String sql = "select *from datacache where _key = '" + key + "'";
		if (db != null) {
			Cursor cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				// 得到数据库中json缓存
				result = cursor.getString(1);
				break;
			}
			cursor.close();
		}
		return result;
	}

	/**
	 * 清除缓存，即删除缓存表，再重新建立一个
	 */
	public synchronized void clearCache(String tableName) {
		if (db == null) {
			return;
		}
		try {
			db.execSQL("drop table " + tableName);
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @see android.database.sqlite.SQLiteOpenHelper#close()
	 */
	@Override
	public synchronized void close() {
		super.close();
		if (db != null) {
			db.close();
		}
	}

}
